﻿"""
中谱补粉机校准系统
"""

# 导入系统与第三方模块
import os
import uuid
from flask import Flask, render_template, redirect, url_for, request, flash, session, send_from_directory
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField, RadioField
from wtforms.validators import DataRequired, Length, Regexp
from flask_wtf.file import FileField, FileRequired, FileAllowed
import pyodbc
import re


# 导入自制模块
from weblog import get_logger
from configprecal import config_precal
from configcoef import config_coef
from configMP import config_mp
from sortingdataprocess import *
from getcobspec import get_mo_list, get_flag, get_days

# 初始化日志功能，启动日志记录
logger = get_logger(__name__)
# logger.info("....")

# Flask实例化，加密Cookie
app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', 'secret string')

# 删除Jinja2语句中的空格和制表符
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

# 上传附件最大10Mb上限
app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024

# 测试机相关ip字典 192.168.3.244
ip_dict = {'127.0.0.1': 20, '192.168.30.101': 1, '192.168.30.57': 2,
           '192.168.30.195': 6, '192.168.30.16': 7, '192.168.30.76': 8, '192.168.30.151': 9,
           '192.168.30.10': 10, '192.168.30.66': 11, '192.168.30.83': 12, '192.168.30.196': 13, '192.168.20.219': 14,
           '192.168.20.133': 15, '192.168.30.108': 16, '192.168.30.3': 17, '192.168.30.179': 18, '192.168.60.98': 19,
           '192.168.30.45': 20, '192.168.30.72': 21
           }
auth_dict = {0: '本机', 3: '中谱连片分光机', 4: '中谱单颗分光机', 5: '中谱一体机', 6: 'ZP1', 7: 'ZP2', 8: 'ZP3', 9: 'ZP4', 10: 'ZP5',
             11: 'ZP6',
             12: 'ZP7', 13: 'ZP8', 20: 'ZP9', 21: 'ZP10'}


# 定义准备校准输入界面表单类，设置相关验证要求
class PrepareCalibration(FlaskForm):
    mo_id1 = StringField('工单号', validators=[DataRequired(), Length(13, 13)])
    user_id1 = StringField('工号', validators=[DataRequired(), Length(5, 5)])
    standard_part1 = StringField('标准件', validators=[DataRequired(), Length(1, 100)])
    excel_data1 = FileField('ini配置文件', validators=[FileRequired(), FileAllowed(['ini'])])
    submit1 = SubmitField('配置下载')


# 定义工单下载输入界面表单类，设置相关验证要求
class GenerateCoefficient(FlaskForm):
    excel_data2 = FileField('无系数数据', validators=[FileRequired(), FileAllowed(['csv'])])
    submit2 = SubmitField('生成系数')


# 定义数据上传表单类，设置相关验证要求
class ProductionValidation(FlaskForm):
    excel_data3 = FileField('加系数数据', validators=[FileRequired(), FileAllowed(['csv'])])
    pes3 = RadioField('中心坐标点', choices=[('1', '小批量湿胶（SD）'), ('2', '量产湿胶（SD）'),
                                        ('3', '量产成品（SD&NM）')], validators=[DataRequired()])
    submit3 = SubmitField('量产验证')


# 创建文件上传临时目录
temp_uploads = os.path.join('uploads', 'temp')
if os.path.exists(temp_uploads):
    pass
else:
    os.makedirs(temp_uploads)


# 定义文件修改函数，新文件名为uuid，用于临时存储
def alter_filename(filename):
    ext = os.path.splitext(filename)[1]
    new_filename = uuid.uuid4().hex + ext
    return new_filename


# 注册/路由和cal视图函数
@app.route('/delete', methods=['GET', 'POST'])
def delete():
    session.clear()
    return redirect(url_for("cal"))

# 注册/路由和cal视图函数
@app.route('/', methods=['GET', 'POST'])
def cal():
    mo_id = request.args.get("mo_id") if request.args.get("mo_id") else None
    standard_part = request.args.get("standard_part") if request.args.get("standard_part") else None
    user_id = request.args.get("user_id") if request.args.get("user_id") else None
    flag_go_on = request.args.get("flag_go_on") if request.args.get("flag_go_on") else False
    form1 = PrepareCalibration()
    form2 = GenerateCoefficient()
    form3 = ProductionValidation()
    # 根据IP地址判定并添加MachineID，服务器ID为0，非分光机台为999
    machine_ip = request.remote_addr
    if machine_ip in ip_dict:
        machine_no = ip_dict[machine_ip]
        if machine_no in auth_dict:
            machine_id = machine_no
        else:
            machine_id = 999
    else:
        machine_id = 999

    # 非授权计算机IP将被重新定向拒绝访问页面
    if machine_id == 999:
        logger.warn(f"{machine_ip} -- 非法访问！")
        return redirect(url_for('reject'))

    # 创建两个数据库的连接,方便进行数据库的查询
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31,1433;'
                          'DATABASE=SortingDB;UID=labins;PWD=labins966')
    conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31,1433;'
                          'DATABASE=SortingDB;UID=qains;PWD=qains8100')
    engine = create_engine(
        "mssql+pyodbc://labins:labins966@172.18.65.31:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
        fast_executemany=True)

    # 步骤一操作
    if form1.submit1.data and form1.validate():
        """
        本部分代码要实现的主要功能：
        1. 获取form1表单相关数据
        2. 获取上传附件的ini配置文件中的Position和Clamp这两部门机械运动控制
        3. 根据标准件的数据生成配置文件，包括电流，电压，运动控制参数，其他参数默认
        """
        df_special = pd.read_excel("ini/型号.xlsx", header=0)
        list_model = list(df_special['StandardpartID'])
        # 获取上传文件信息
        f = form1.excel_data1.data
        logger.info(f"第一步上传原始数据：{f.filename}")
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename)
        temp_path = os.path.join(temp_uploads, filename)
        # 根据路径保存文件
        f.save(temp_path)
        # 单号的验证
        if form1.mo_id1.data not in get_mo_list():
            return f"<p>工单号不存在于MO_ITEM表中，请检查工单号是否正确！！</p> " \
                   + f'<br><a href="{url_for("cal")}">返回</a>'
        # 将表单相关信息存为字典，方便数据处理,依次是工单号，机台号，工号，工序，标准件，上传文件数据
        form1_infos = {
            'MoID': form1.mo_id1.data,
            'MachineID': machine_id, 'UserID':form1.user_id1.data,
            'StandardPartID': form1.standard_part1.data, 'temp_path': temp_path,
            'UploadOriginalFileName': f.filename}
        logger.info(f"判定标志:{get_flag(form1_infos['StandardPartID'])}")
        logger.info(f"距今天数:{get_days(form1_infos['StandardPartID'])}")
        if not flag_go_on:
            # 该标准件不在分光中
            if not get_flag(form1.standard_part1.data):
                days = get_days(form1.standard_part1.data)
                if days is None:
                    return f"<p>该标准件无有效数据</p><br>" \
                           f"<a href='{url_for('cal')}'>返回</a>"
                elif form1.standard_part1.data not in list_model and days > 20:
                    return f"<p>该标准件已过期，请联系实验室进行更新</p><br>" \
                           f"<a href='{url_for('cal', mo_id=form1.mo_id1.data, standard_part=form1.standard_part1.data, user_id=form1.user_id1.data, flag_go_on=True)}'>继续</a><br>" \
                           f"<a href='{url_for('cal')}'>返回</a>"
                elif form1.standard_part1.data in list_model and days > 10:
                    return f"<p>该标准件已过期，请联系实验室进行更新</p><br>" \
                           f"<a href='{url_for('cal', mo_id=form1.mo_id1.data, standard_part=form1.standard_part1.data, user_id=form1.user_id1.data, flag_go_on=True)}'>继续</a><br>" \
                           f"<a href='{url_for('cal')}'>返回</a>"

        # 调用configprecal中定义好的config_precal函数
        flash_result, initconf_path, lotID = config_precal(form1_infos, cnxn, logger)

        # 成功,检验步骤一生成配置文件是否存在,步骤1的批次号是否存在
        if '成功' in flash_result and initconf_path is not None and lotID is not None:
            # 在浏览器保存步骤一中的数据,方便下一步
            session[f"{machine_id}mo_id"] = form1.mo_id1.data
            session[f"{machine_id}standard_part"] = form1.standard_part1.data
            session[f"{machine_id}user_id"] = form1.user_id1.data
            session[f"{machine_id}lotID"] = lotID
            session[f'{machine_id}initconf_path'] = initconf_path
            return send_from_directory(os.path.split(initconf_path)[0],
                                       filename=os.path.split(initconf_path)[1], as_attachment=True)
        # 失败,返回自定义的错误信息,并添加返回按钮
        else:
            # 步骤一数据赋值为空
            session[f"{machine_id}mo_id"] = None
            session[f"{machine_id}standard_part"] = None
            session[f"{machine_id}user_id"] = None
            session[f"{machine_id}lotID"] = None
            session[f'{machine_id}initconf_path'] = None
            return f"<p>{''.join(list(flash_result.values()))}</p> " \
                   + f'<br><a href="{url_for("cal")}">返回</a>'

    if form2.submit2.data and form2.validate():
        """
        本部分代码要实现的主要功能：
        1. 获取form2表单相关数据
        2. 更新ini文件中的dU，dx，dy，dlm，dRa，dR9
        """
        # 检验downloads/iniconf/下是否存在步骤一生成的配置文件
        try:
            flag = session.get(f"{machine_id}flag")
            if session.get(f"{machine_id}lotID") is None or session.get(f"{machine_id}initconf_path") is None:
                logger.warn(f"{machine_id}号设备，未进行第一步！！")
                return f"<p>请执行步骤1：输入工单相关信息并上传机台ini配置文件</p> " \
                       + f'<br><a href="{url_for("cal")}">返回</a>'
        except Exception as e:
            logger.error(f"{machine_id}号设备出现问题,{e}")
            return f"<p>第一步验证不通过,问题描述：{e}</p> " \
                   + f'<br><a href="{url_for("cal")}">返回</a>'
        # 获取上传文件信息,名称
        f = form2.excel_data2.data
        logger.info(f"第二步上传原始数据：{f.filename}")
        fname = os.path.splitext(f.filename)[0]
        new_name = fname.split('-')[-1]
        ret = re.match('[0]{1,3}', new_name)
        if len(new_name) > 3:
            if ret:
                logger.warn(f"{machine_id}号设备,{session.get(f'{machine_id}mo_id')},该工单已校准超过三次！")
            else:
                logger.warn(f"{machine_id}号设备,{session.get(f'{machine_id}mo_id')}--校准1数据命名不规范")
                return f"<p>上传文件命名不规范，请按照规范命名,以一个或者多个‘0’结尾！！</p> " \
                       + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'
        else:
            if not ret:
                logger.warn(f"{machine_id}号设备,{session.get(f'{machine_id}mo_id')}--校准1数据命名不规范")
                return f"<p>上传文件命名不规范，请按照规范命名,以一个或者多个‘0’结尾！！</p> " \
                       + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename)
        temp_path = os.path.join(temp_uploads, filename)
        # 根据路径保存文件
        f.save(temp_path)
        form2_infos = {'MoID': session.get(f"{machine_id}mo_id"), 'LotID': session.get(f"{machine_id}lotID"),
                       'MachineID': machine_id,
                       'UserID': session.get(f"{machine_id}user_id"),
                       'StandardPartID': session.get(f"{machine_id}standard_part"),
                       'temp_path': temp_path, 'UploadOriginalFileName': f.filename,
                       'InitPath': session.get(f"{machine_id}initconf_path")}
        # qpp.py中调用configcoef中的 config_coef 函数
        flash_result, coefconf_path, lotID2, pre_path = config_coef(form2_infos, cnxn, conn, engine, logger, flag)

        # 如果成功,且成功存储第二步生成配置文件,批次号与系数都存在的情况下,让浏览器下载配置文件
        if '成功' in flash_result and coefconf_path is not None and lotID2 is not None and pre_path is not None:
            # 保存步骤二生成的数据
            session[f"{machine_id}lotID2"] = lotID2
            session[f"{machine_id}pre_path"] = pre_path
            session[f"{machine_id}coefconf_path"] = coefconf_path
            # print(lotID2)
            return send_from_directory(os.path.split(coefconf_path)[0],
                                       filename=os.path.split(coefconf_path)[1], as_attachment=True)
        # 如果失败,清空存储的信息,返回错误信息,并添加返回按钮
        else:
            if 6 in flash_result.keys():
                session[f"{machine_id}lotID2"] = None
                session[f"{machine_id}pre_path"] = None
                session[f"{machine_id}coefconf_path"] = None
                session[f"{machine_id}flag"] = True
            else:
                # 步骤二数据赋值为空
                session[f"{machine_id}lotID2"] = None
                session[f"{machine_id}pre_path"] = None
                session[f"{machine_id}coefconf_path"] = None
                session[f"{machine_id}flag"] = None
            return f"<p>{''.join(list(flash_result.values()))}</p> " \
                   + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'

    if form3.submit3.data and form3.validate():
        """
               本部分代码要实现的主要功能：
               1. 获取form3表单相关数据
               2. 判定校准是否通过
               3. 校准通过更新ini文件中的dU，dx，dy，dlm，dRa，dR9，中心点xy及abθ
               """
        # 检验downloads/coefconf/下是否存在步骤二生成的配置文件
        try:
            pre_path = session.get(f"{machine_id}pre_path")
            if not pre_path:
                pre_path = request.args.get('pre_path')
            else:
                session.pop(f'{machine_id}pre_path')
            if session.get(f"{machine_id}lotID2") is None or pre_path is None:
                logger.warn(f"{machine_id}号设备，未进行第二步！！")
                return f"<p>请执行步骤2：确保生成有效数据</p> " \
                       + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'
        except Exception as e:
            logger.error(f"{machine_id}号设备出现问题,{e}")
            return f"<p>问题描述：{e}</p> " \
                   + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'
        # 获取上传文件信息
        f = form3.excel_data3.data
        logger.info(f"第三步上传原始数据：{f.filename},使用第二步数据为{pre_path}")
        fname = os.path.splitext(f.filename)[0]
        new_name = fname.split('-')[-1]
        ret = re.match('1{1,3}', new_name)
        if len(new_name) > 3:
            if ret:
                logger.warn(f"{machine_id}号设备,{form1.mo_id1},该工单已校准超过三次！")
            else:
                logger.warn(f"{machine_id}号设备,{session.get(f'{machine_id}mo_id')}--校准2数据命名不规范")
                return f"<p>上传文件命名不规范，请按照规范命名,以一个或者多个‘1’结尾！！</p> " \
                       + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'
        else:
            if not ret:
                logger.warn(f"{machine_id}号设备,{session.get(f'{machine_id}mo_id')}--校准2数据命名不规范")
                return f"<p>上传文件命名不规范，请按照规范命名,以一个或者多个‘1’结尾！！</p> " \
                       + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"))}">返回</a>'
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename)
        temp_path = os.path.join(temp_uploads, filename)
        # 根据路径保存文件
        f.save(temp_path)
        # 将web相关session信息存为字典
        form3_infos = {'MoID': session.get(f"{machine_id}mo_id"), 'LotID': session.get(f"{machine_id}lotID"),
                       'LotID2': session.get(f"{machine_id}lotID2"),
                       'MachineID': machine_id, 'UserID': session.get(f"{machine_id}user_id"),
                       'StandardPartID': session.get(f"{machine_id}standard_part"),
                       'temp_path': temp_path, 'UploadOriginalFileName': f.filename,
                       'ProcessID': form3.pes3.data, 'CoePath': session.get(f"{machine_id}coefconf_path"),
                       'coef_data': session.get(f"{machine_id}coef_data")}
        # app.py中调用configPM.py中的config_mp函数
        flash_result, mpconf_path, lotID3 = config_mp(form3_infos, pre_path, cnxn, conn, engine, logger)
        # 成功,检验步骤三生成的文件是否存在,第三步批次号是否存在,存在让浏览器进行下载
        if '成功' in flash_result and mpconf_path is not None and lotID3 is not None:
            # session.clear()
            return send_from_directory(os.path.split(mpconf_path)[0],
                                       filename=os.path.split(mpconf_path)[1], as_attachment=True)
        # 返回异常数据表格,并添加返回按钮
        elif '校准异常' in flash_result:
            return '<p>标准件测试异常数据</p>' + \
                   flash_result['校准异常'].to_html(header="true") + \
                   f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"), pre_path=pre_path)}">返回</a>'
        # 返回错误信息,并添加返回按钮
        else:
            return f"<p>{''.join(list(flash_result.values()))}</p> " \
                   + f'<br><a href="{url_for("cal", mo_id=session.get(f"{machine_id}mo_id"), standard_part=session.get(f"{machine_id}standard_part"), user_id=session.get(f"{machine_id}user_id"), pre_path=pre_path)}">返回</a>'

    # 渲染出cal.html这个页面(让页面显示在浏览器上)
    return render_template('cal.html', form1=form1, form2=form2, form3=form3,
                           mo_id=session.get(f"{machine_id}mo_id"),
                           standard_part=session.get(f"{machine_id}standard_part"),
                           user_id=session.get(f"{machine_id}user_id"),
                           lotID=session.get(f"{machine_id}lotID"),
                           lotID2=session.get(f"{machine_id}lotID2"), lotID3=session.get(f"{machine_id}lotID3"))


# 注册/reject路由和reject视图函数
@app.route('/reject', methods=['GET'])
def reject():
    return render_template('reject.html')


# 主程序入口
if __name__ == '__main__':
    app.run(debug=True)
